MDX Function Listing
The following guide is a summary of the functions available in the Pyramid MDX Editor. The descriptions have been derived from the online MDX reference published by Microsoft.
For more detail on the structure of each function and how it should be used please check this reference and other third party materials.
MDX Function Name | Grouping | Type | Description |
---|---|---|---|
AddCalculatedMembers | Set | function | Returns a set generated by adding calculated members to a specified set. |
Aggregate | Numeric | function | Returns a scalar value calculated by aggregating either measures or an optionally specified numeric expression over the tuples of a specified set. |
AllMembers | Dimension | property | Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level. |
AllMembers | Hierarchy | property | Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level. |
AllMembers | Level | property | Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level. |
Ancestor | Member | function | Returns the ancestor of a member at a specified level or distance. |
Ancestors | Set | function | Returns a set of all ancestors of a member at a specified level or distance. |
Ascendants | Set | function | Returns the set of the ascendants of a specified member, including the member itself. |
Avg | Numeric | function | Returns the average value of measures or the average value of an optional numeric expression, evaluated over a specified set. |
Axis | Set | function | Returns a set defined in an axis. |
BottomCount | Set | function | Sorts a set in ascending order, and returns the specified number of tuples with the lowest values. |
BottomPercent | Set | function | Sorts a set in ascending order, and returns a set of tuples with the lowest values whose cumulative total is equal to or less than a specified percentage. |
BottomSum | Set | function | Sorts a set in ascending order, and returns a set of tuples with the lowest values whose total is equal to or less than a specified value. |
CalculationCurrentPass | Numeric | function | Returns the current calculation pass of a cube for the specified query context. |
CalculationPassValue | Numeric | function | Returns the value of a MDX expression evaluated over the specified calculation pass of a cube. |
CalculationPassValue | Numeric | function | Returns the value of a MDX expression evaluated over the specified calculation pass of a cube. |
Children | Set | property | Returns the children of a specified member. |
ClosingPeriod | Member | function | Returns the last sibling among the descendants of a member at a specified level. |
CoalesceEmpty | Numeric | function | Coalesces an empty cell value to a number or string and returns the coalesced value. |
Correlation | Numeric | function | Returns the correlation coefficient of two series evaluated over a set. |
Count | Numeric | function | Returns the number of cells in a set. |
Count | Numeric | property | Returns the number of cells in a set. |
Cousin | Member | function | Returns the child member with the same relative position under a parent member as the specified child member. |
Covariance | Numeric | function | Returns the population covariance of two series evaluated over a set, using the biased population formula. |
CovarianceN | Numeric | function | Returns the sample covariance of two series evaluated over a set, using the unbiased population formula. |
Crossjoin | Set | function | Returns the cross product of one or more sets. |
Current | Member | property | Returns the current tuple from a set during iteration. |
CurrentMember | Dimension | property | Returns the current member along a specified dimension or hierarchy during iteration. |
CurrentMember | Hierarchy | property | Returns the current member along a specified dimension or hierarchy during iteration. |
CurrentOrdinal | Numeric | property | Returns the current iteration number within a set during iteration. |
DataMember | Numeric | property | Returns the system-generated data member that is associated with a non-leaf member of a dimension. |
DefaultMember | Member | property | Returns the default member of a dimension or hierarchy. |
DefaultMember | Member | property | Returns the default member of a dimension or hierarchy. |
Descendants | Set | function | Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels. |
Descendants | Set | function | Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels. |
Dimension | Hierarchy | property | Returns the dimension that contains a specified member, level, or hierarchy. |
Dimension | Level | property | Returns the dimension that contains a specified member, level, or hierarchy. |
Dimension | Member | property | Returns the dimension that contains a specified member, level, or hierarchy. |
Dimensions | Dimension | function | Returns a hierarchy specified by a numeric or string expression. |
Distinct | Set | function | Returns a set, removing duplicate tuples from a specified set. |
DistinctCount | Numeric | function | Returns the number of distinct, nonempty tuples in a set. |
DrilldownLevel | Set | function | Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set. |
DrilldownLevel | Set | function | Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set. |
DrilldownLevelBottom | Set | function | Drills down the bottommost members of a set, at a specified level, to one level below. |
DrilldownLevelTop | Set | function | Drills down the topmost members of a set, at a specified level, to one level below. |
DrilldownMember | Set | function | Drills down the members in a specified set that are present in a second specified set. Alternatively, the function drills down on a set of tuples. |
DrilldownMemberBottom | Set | function | Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members. Alternatively, this function also drills down on a set of tuples. |
DrilldownMemberTop | Set | function | Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members. Alternatively, this function drills down on a set of tuples. |
DrillupLevel | Set | function | Drills up the members of a set that are below a specified level. |
DrillupMember | Set | function | Drills up the members in a specified set that are present in a second specified set. |
Error | Logical | function | Raises an error, optionally providing a specified error message. |
Except | Set | function | Finds the difference between two sets, optionally retaining duplicates. |
Exists | Set | function | Returns the set of members of one set that exist with one or more tuples of one or more other sets. |
Extract | Set | function | Returns a set of tuples from extracted dimension elements. |
Filter | Set | function | Returns the set that results from filtering a specified set based on a search condition. |
FirstChild | Member | property | Returns the first child of a member. |
FirstSibling | Member | property | Returns the first child of the parent of a member. |
Generate | Set | function | Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set. |
Generate | Set | function | Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set. |
Head | Set | function | Returns the first specified number of elements in a set, while retaining duplicates. |
Hierarchize | Set | function | Orders the members of a set in a hierarchy. |
Hierarchy | Level | property | Returns the hierarchy that contains a specified member or level. |
Hierarchy | Member | property | Returns the hierarchy that contains a specified member or level. |
IIf | Logical | function | Returns one of two values determined by a logical test. |
Intersect | Set | function | Returns the intersection of two input sets, optionally retaining duplicates. |
IsAncestor | Logical | function | Returns whether a specified member is an ancestor of another specified member. |
IsEmpty | Logical | function | Returns whether the evaluated expression is the empty cell value. |
IsGeneration | Logical | function | Returns whether a specified member is in a specified generation. |
IsLeaf | Logical | function | Returns whether a specified member is a leaf member. |
IsSibling | Logical | function | Returns whether a specified member is a sibling of another specified member. |
Item | Member | property | Returns a member from a specified tuple. |
KPICurrentTimeMember | KPI | function | Returns the current time member of the specified Key Performance Indicator (KPI). |
KPIGoal | KPI | function | Returns the member that calculates the value for the goal portion of the specified Key Performance Indicator (KPI). |
KPIStatus | KPI | function | Returns the member that calculates the value for the status portion of the specified Key Performance Indicator (KPI). |
KPITrend | KPI | function | Returns the member that calculates the value for the trend portion of the specified Key Performance Indicator (KPI). |
KPIValue | KPI | function | Returns the member that calculates the value for the value portion of the specified Key Performance Indicator (KPI). |
KPIWeight | KPI | function | Returns the weight of the specified Key Performance Indicator (KPI). |
Lag | Member | property | Returns the member that is a specified number of positions before a specified member along the member's dimension. |
LastChild | Member | property | Returns the last child of a specified member. |
LastPeriods | Set | function | Returns a set of members up to and including a specified member. |
LastSibling | Member | property | Returns the last child of the parent of a specified member. |
Lead | Member | property | Returns the member that is a specified number of positions following a specified member along the member's dimension. |
Leaves | Set | function | Returns the set of leaf members in the specified dimension, member, or tuple. |
Level | Level | property | Returns the level of a member. |
Levels | Member | property | Returns the level whose position in a dimension or hierarchy is specified by a numeric expression or whose name is specified by a string expression. |
LinkMember | Member | function | Returns the member equivalent to a specified member in a specified hierarchy. |
LinRegIntercept | Numeric | function | Calculates the linear regression of a set and returns the value of the intercept in the regression line, y = ax + b. |
LinRegPoint | Numeric | function | Calculates the linear regression of a set and returns the value of y in the regression line, y = ax + b. |
LinRegR2 | Numeric | function | Calculates the linear regression of a set and returns the coefficient of determination, R2. |
LinRegSlope | Numeric | function | Calculates the linear regression of a set, and returns the value of the slope in the regression line, y = ax + b. |
LinRegVariance | Numeric | function | Calculates the linear regression of a set, and returns the variance associated with the regression line, y = ax + b. |
LookupCube | Member | function | Returns the value of an MDX expression evaluated over another specified cube in the same database. |
Max | Numeric | function | Returns the maximum value of a numeric expression that is evaluated over a set. |
MeasureGroupMeasures | Set | function | Returns a set of measures that belongs to the specified measure group. |
Median | Numeric | function | Returns the median value of a numeric expression that is evaluated over a set. |
Members | Dimension | property | Returns the set of members in a dimension, level, or hierarchy. |
Members | Hierarchy | property | Returns the set of members in a dimension, level, or hierarchy. |
Members | Level | property | Returns the set of members in a dimension, level, or hierarchy. |
Members | Set | function | Returns the set of members in a dimension, level, or hierarchy. |
MemberToStr | String | function | Returns an MDX–formatted string that corresponds to a specified member. |
Min | Numeric | function | Returns the minimum value of a numeric expression that is evaluated over a set. |
Mtd | Set | function | Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension. |
Name | Dimension | property | Returns the name of a dimension, hierarchy, level, or member. |
Name | Hierarchy | property | Returns the name of a dimension, hierarchy, level, or member. |
Name | Level | property | Returns the name of a dimension, hierarchy, level, or member. |
Name | Member | property | Returns the name of a dimension, hierarchy, level, or member. |
NameToSet | Set | function | Returns a set that contains the member specified by an MDX–formatted string. |
NextMember | Member | property | Returns the next member in the level that contains a specified member. |
NonEmptyCrossjoin | Set | function | Returns the cross product of one or more sets as a set, excluding empty tuples and tuples without associated fact table data. |
OpeningPeriod | Member | function | Returns the first sibling among the descendants of a specified level, optionally at a specified member. |
Order | Set | function | Arranges members of a specified set, optionally preserving or breaking the hierarchy. |
Ordinal | Numeric | property | Returns the zero-based ordinal value associated with a level. |
ParallelPeriod | Member | function | Returns a member from a prior period in the same relative position as a specified member. |
Parent | Member | property | Returns the parent of a member. |
PeriodsToDate | Set | function | Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension. |
Predict | Numeric | function | Returns a value of a numeric expression evaluated over a data mining model. |
PrevMember | Member | property | Returns the previous member in the level that contains a specified member. |
Properties | Numeric | property | Returns a string, or a strongly-typed value, that contains a member property value. |
Qtd | Set | function | Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Quarter level in the Time dimension. |
Rank | Numeric | function | Returns the one-based rank of a specified tuple in a specified set. |
RollupChildren | Numeric | function | Returns a value generated by rolling up the values of the children of a specified member using the specified unary operator. |
Root | Member | function | Returns a tuple that consists of the All members from each attribute hierarchy in a cube, dimension, or tuple. |
SetToArray | Other | function | Converts one or more sets to an array for use in a user-defined function. |
SetToStr | String | function | Returns an MDX-formatted string of that corresponds to a specified set. |
Siblings | Set | property | Returns the siblings of a specified member, including the member itself. |
Stddev | Numeric | function | Alias for Stdev (MDX). |
StddevP | Numeric | function | Alias for StdevP (MDX). |
Stdev | Numeric | function | Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula. |
StdevP | Numeric | function | Returns the population standard deviation of a numeric expression evaluated over a set, using the biased population formula. |
StripCalculatedMembers | Set | function | Returns a set generated by removing calculated members from a specified set. |
StrToMember | Member | function | Returns the member specified by an MDX–formatted string. |
StrToSet | Set | function | Returns the set specified by an MDX–formatted string. |
StrToTuple | Tuple | function | Returns the tuple specified by an MDX–formatted string. |
StrToValue | Numeric | function | Returns the value specified by an MDX–formatted string. |
Subset | Set | function | Returns a subset of tuples from a specified set. |
Sum | Numeric | function | Returns the sum of a numeric expression evaluated over a set. |
Tail | Set | function | Returns a subset from the end of a set. |
ToggleDrillState | Set | function | Toggles the drill state of members. |
TopCount | Set | function | Sorts a set in descending order and returns the specified number of elements with the highest values. |
TopPercent | Set | function | Sorts a set in descending order, and returns a set of tuples with the highest values whose cumulative total is equal to or less than a specified percentage. |
TopSum | Set | function | Sorts a set and returns the topmost elements whose cumulative total is at least a specified value. |
TupleToStr | Tuple | function | Returns an MDX–formatted string that corresponds to specified tuple. |
Union | Set | function | Returns the union of two sets, optionally retaining duplicates. |
UniqueName | Dimension | property | Returns the unique name of a specified dimension, hierarchy, level, or member. |
UniqueName | Hierarchy | property | Returns the unique name of a specified dimension, hierarchy, level, or member. |
UniqueName | Level | property | Returns the unique name of a specified dimension, hierarchy, level, or member. |
UniqueName | Member | property | Returns the unique name of a specified dimension, hierarchy, level, or member. |
UnknownMember | Member | property | Returns the unknown member associated with a level or member. |
UnknownMember | Member | property | Returns the unknown member associated with a level or member. |
Unorder | Set | function | Removes any enforced ordering from a specified set. |
UserName | String | function | Returns the domain name and user name of the current connection. |
ValidMeasure | Member | function | Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level. |
Value | Member | property | Returns the value of a measure. |
Var | Numeric | function | Returns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula. |
Variance | Numeric | function | Alias for Var (MDX). |
VarianceP | Numeric | function | Alias for VarP (MDX). |
VarP | Numeric | function | Returns the population variance of a numeric expression evaluated over a set, using the biased population formula. |
VisualTotals | Set | function | Returns a set generated by dynamically totaling child members in a specified set, optionally using a pattern for the name of the parent member in the resulting cellset. |
Wtd | Set | function | Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Week level in the Time dimension. |
Ytd | Set | function | Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension. |
Home |
Table of Contents |
Index |
User Community
Pyramid Analytics © 2011-2022